package c.x.jy.common.utils;

import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSON;
@SuppressWarnings("all")
public class SqlBuilder {
	  private StringBuilder sqlsfrom = new StringBuilder();
    private StringBuilder sqls = new StringBuilder();
    private StringBuilder orderBy = new StringBuilder();
    private StringBuilder limit = new StringBuilder();
    private StringBuilder groupBy = new StringBuilder();
    private List<Object> params = new ArrayList();
    private List<Object> fromparams = new ArrayList();
    private static final String AND = " AND ";
    private static final String OR = " OR ";
    private static final String GROUP_BY = " GROUP BY ";
    private static final String ORDER_BY = " ORDER BY ";
    private static final String HAVING = " HAVING ";
    private static final String LIMIT = " LIMIT ";
    
   // private static final String LIKE = " LIKE ";

    public SqlBuilder(String sql,Object... param){
    	if(sql!=null){
    		sqlsfrom.append(sql);
	        if(param!=null) {
	            for (Object o : param) {
	            	fromparams.add(o);
	            }
	        }
    	}
    }
    /**
     * 新FROM语句
     * @param sql
     * @param param
     * @param y
     * @return
     */
    
    public SqlBuilder andnewFrom(String sql,boolean y,Object... param){
        if(y)
        	if(sql!=null){
        		sqlsfrom=new StringBuilder();
        		fromparams = new ArrayList();
        		sqlsfrom.append(sql);
    	        if(param!=null) {
    	            for (Object o : param) {
    	            	fromparams.add(o);
    	            }
    	        }
        	}
        return this;
    }
    public SqlBuilder andnewFrom(String sql, Object... param){
    	return andnewFrom(sql,true, param);
    }
    public SqlBuilder append(String sql,Object param){
        _add(sql, param, " ");
        return this;
    }

    public SqlBuilder and(String sql,Object param,boolean y){
        if(y)
    	_add(sql, param, AND);
        return this;
    }
    public SqlBuilder and(String sql,Object param ){
       
    	_add(sql, param, AND);
        return this;
    }

    public SqlBuilder or(String sql,Object param){
        _add(sql, param, OR);
        return this;
    }

    public SqlBuilder groupBy(String sql){
        _add(sql,"", GROUP_BY);
        return this;
    }

    public SqlBuilder orderBy(String sql){
        _add(sql,"", ORDER_BY);
        return this;
    }

    public SqlBuilder having(String sql,Object param){
        _add(sql,param, HAVING);
        return this;
    }

    public SqlBuilder limit(Integer size){
        _add(" ? ",size, LIMIT);
        return this;
    }
    public SqlBuilder limit(Integer size,boolean r){
    	if(r)
        _add(" ? ",size, LIMIT);
        return this;
    }
    public SqlBuilder limit(Integer size,Integer offset){
        _add(" ? ",size, LIMIT);
        _add(" ? ",offset, ",");
        return this;
    }

    private void _add(String sql, Object param, String connect) {
        if(param!=null){
        	StringBuilder sb; 
        	if(connect.equals(ORDER_BY))sb=orderBy;
        	else if(connect.equals(GROUP_BY))sb=groupBy;
        	else if(connect.equals(LIMIT))sb=limit;
        	else sb=sqls;
            if(param instanceof List){
              String p="";List list=(List) param;
            	for (Object object : list) {
            		p+=p.equals("")?"("+object:","+object;
				}
            	p+=")";
            	sql=sql.replace("?", p);
            }
            sb.append(connect).append(sql);
            int n = getCharCount(sql,'?');
            for(int i=0;i<n;i++){
                params.add(param);
            }
        }
    }
    /**
     * 
     * @param sql
     * @param connect
     * @param param
     */
    private void _add1(String sql,  String connect,Object... param) {
        if(param!=null){
        	StringBuilder sb; 
        	if(connect.equals(ORDER_BY))sb=orderBy;
        	else if(connect.equals(GROUP_BY))sb=groupBy;
        	else sb=sqls;
            sb.append(connect).append(sql);
            int n = getCharCount(sql,'?');
            for(int i=0;i<n;i++){
                params.add(param[i]);
            }
        }
    }

    private int getCharCount(String s,char c){
        int n = 0;
        char[] sc = s.toCharArray();
        for(int i=0;i<sc.length;i++){
            if(sc[i]==c){
                n++;
            }
        }
        return n;
    }

    public String toString(){
    	StringBuilder sql=new StringBuilder(sqlsfrom);
       // System.out.println(sqls.toString()+groupBy.toString()+orderBy.toString()+"|" + JSON.toJSONString(params.toArray()));
        return sql.append(sqls).append(groupBy).append(orderBy).append(limit).toString();
    }

  
	public Object[] getParam(){
    	     List<Object>  params = new ArrayList();
    	     params.addAll(this.fromparams);
	         params.addAll(this.params);
	  
        return params.toArray();
    }

    public static void main(String[] args){
    /*    SqlBuilder sqlBuilder = new SqlBuilder("from table where 1=1",null);
        sqlBuilder.and("id=?",1)
                .append(" and ( name=?","wgr")
                .or("sex=? )",0)
                .and("a like \'%?%\'", 11)
                .groupBy("sex")
                .orderBy("name")
               
                .limit(5,10);*/
    	
    	/*SqlBuilder sqlBuilder = new SqlBuilder("select * from coupon_order co WHERE 1=1 ");
       // List<Object>  params = new ArrayList();
       // params.add("a");params.add("b");
    	sqlBuilder.andnewFrom("select * from coupon_order co WHERE 1=? and 3=?","a","b" );
		sqlBuilder.and("co.status=?",1);
		sqlBuilder.and("co.delivery_address=?", 2,true);
		sqlBuilder.and("co.area like \'?%\'", 3,false);
		sqlBuilder.and("co.pay_time between ? and ?",true, 4,5 );
		sqlBuilder.orderBy("co.create_time desc");*/
    	Integer i=1;
    	Integer j=3;
    	String keys="1";
    	SqlBuilder sqlBuilder = new SqlBuilder(" from cms_article where del_flag=0  ");
		sqlBuilder.and("( category_ids like   concat('%\"',?,'\"%')  ", i,i!=null);
		sqlBuilder.or("category_ids like   concat('%\"',?,'\"%')  ", 2);
		sqlBuilder.or("category_ids like   concat('%\"',?,'\"%') ) ", 3);
		sqlBuilder.and(" business_id=? ", j,j!=null);
		
		sqlBuilder.and(" ( keywords like  concat('%',?,'%') or title like  concat('%',?,'%')  )", keys,!StringTools.isEmpty(keys));
		sqlBuilder.orderBy(" weight desc ");
		sqlBuilder.limit(1);
    System.out.println(sqlBuilder.toString()+"|" + JSON.toJSONString(sqlBuilder.getParam()));
        
      
        SqlBuilder sqlBuilder1 = new SqlBuilder("select * from goods_category where business_id = ? and status=1 "  ,i);
		sqlBuilder1.orderBy(" `order` asc ");
		//sqlBuilder.and(" LIMIT 1 ", pageSize!=null, pageSize);
		sqlBuilder1.limit(j);
		  System.out.println(sqlBuilder1.toString()+"|" + JSON.toJSONString(sqlBuilder1.getParam()));
    }

	public SqlBuilder and(String sql, boolean b,Object... param) {
		if(b)
	    	_add1(sql,AND, param);
		return this;
	}

/*	public SqlBuilder like(String sql,  Object param, boolean blank) {
	 
		if(blank)_add(sql, param, LIKE);
		return this;
	}
	
	public SqlBuilder like(String sql,  Object param ) {
		 
		 _add(sql, param, LIKE);
		return this;
	}*/
}
